Assume you are given the table containing
information on Amazon customers and their spending on products in various
categories.
Identify the top two highest-grossing products
within each category in 2022. Output the category, product, and total spend.
table name: product_spend
Solution:
with cte as
(
select category,product, sum(spend) as total_spend,rank() over (partition by category order by sum(spend) desc) as rnk from product_spend
where year(transaction_date)=2022
group by category,product
)
select distinct category,product,total_spend from cte where rnk =1 or rnk =2
order by category,total_spend DESC
Output:
SQL Script:
CREATE TABLE [dbo].[product_spend](
[category]
[nvarchar](50) NULL,
[product]
[nvarchar](50) NULL,
[user_id]
[int] NULL,
[spend]
[decimal](18, 2) NULL,
[transaction_date]
[date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[product_spend] ([category], [product], [user_id], [spend], [transaction_date]) VALUES (N'appliance', N'refrigerator', 165, CAST(246.00 AS Decimal(18, 2)), CAST(N'2021-12-26' AS Date))
GO
INSERT [dbo].[product_spend] ([category], [product], [user_id], [spend], [transaction_date]) VALUES (N'appliance', N'refrigerator', 123, CAST(299.99 AS Decimal(18, 2)), CAST(N'2022-02-03' AS Date))
GO
INSERT [dbo].[product_spend] ([category], [product], [user_id], [spend], [transaction_date]) VALUES (N'appliance', N'washing
machine', 123, CAST(219.80 AS Decimal(18, 2)), CAST(N'2022-02-03' AS Date))
GO
INSERT [dbo].[product_spend] ([category], [product], [user_id], [spend], [transaction_date]) VALUES (N'electronics', N'vacuum', 178, CAST(152.00 AS Decimal(18, 2)), CAST(N'2022-05-04' AS Date))
GO
INSERT [dbo].[product_spend] ([category], [product], [user_id], [spend], [transaction_date]) VALUES (N'electronics', N'wireless
headset', 156, CAST(249.90 AS Decimal(18, 2)), CAST(N'2022-08-07' AS Date))
GO
INSERT [dbo].[product_spend] ([category], [product], [user_id], [spend], [transaction_date]) VALUES (N'electronics', N'vacuum', 145, CAST(189.00 AS Decimal(18, 2)), CAST(N'2022-07-15' AS Date))
GO